﻿using MSharp.Data.SPI;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Collections.Specialized;
using MSharp.Data.DatabaseInfo;
using System.Reflection;

namespace MSharp.Data
{
    public class DB : IDB
    {
        /// <summary>
        /// 数据提供程序工厂
        /// </summary>
        public DbProviderFactory DBFactory
        {
            get;
            private set;
        }

        /// <summary>
        /// 连接字符串基类对象
        /// </summary>
        public DbConnectionStringBuilder ConnectionStringBuilder { get; private set; }

        /// <summary>
        /// 数据库类型
        /// </summary>
        public DBType DBType
        {
            get; private set;
        }

        /// <summary>
        /// 数据库表列相关信息
        /// </summary>
        public IDBInfo Info
        {
            get;
            protected set;
        }

        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString { get; private set; }

        /// <summary>
        /// 执行前 
        /// </summary>
        public Action<string, DbParameterCollection> OnExecuting { get; set; }

        /// <summary>
        /// 执行前更改原始 Sql命令或参数 
        /// </summary>
        public Func<string, DbParameterCollection, KeyValuePair<string, DbParameter[]>> OnExecutingChange { get; set; }

        /// <summary>
        /// 执行后，附带返回值
        /// </summary>
        public Action<string, DbParameterCollection, object> OnExecuted { get; set; }

        /// <summary>
        /// 报异常时
        /// </summary>
        public  Action<string, DbParameterCollection, Exception> OnError { get; set; }

        /// <summary>
        /// 插入时 要排除表列 集合
        /// </summary>
        public ExcludeColumnList ExcludeInsertColumns
        {
            get;
            internal set;
        }

        /// <summary>
        /// 获取 插入时排除表列 集合
        /// </summary>
        /// <returns></returns>
        public NameValueCollection GetExcludeInsertColumns()
        {
            return ExcludeInsertColumns.Coll;
        }
        

        /// <summary>
        /// 创建 Connection 对象
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateConn()
        {
            return CreateConn(this.ConnectionString);
        }

        /// <summary>
        /// 构建 参数化 对象 
        /// 不同数据库 参数化 时所使用的字符：Global.ParameterCharMap
        /// </summary>
        /// <param name="parameterName">参数化 变量名</param>
        /// <param name="value">参数化 值</param>
        /// <returns></returns>
        public DbParameter CreateParameter(string parameterName, object value, ColumnInfo colInfo = null)
        {
            DbParameter dbparameter = DBFactory.CreateParameter();
            dbparameter.ParameterName = DBType.ParameterChar() + parameterName;
            dbparameter.Value = value;

            if (value == null || value == DBNull.Value)
            {
                dbparameter.Value = DBNull.Value;
            }
            else
            {
                DbType dbType;
                if (colInfo != null)
                {
                    dbType = DBType.GetDbType(colInfo);                  
                    dbparameter.Value = Global.Dict_Convert_Type[dbType].Invoke(dbparameter.Value);
                    dbparameter.DbType = dbType;
                }
                else
                {
                    Type tyValue = dbparameter.Value.GetType();
                    if (Global.TypeMap.TryGetValue(tyValue, out dbType))
                    {
                        dbparameter.DbType = dbType;
                    }
                    else
                    {
                        dbparameter.DbType = DbType.AnsiString;
                    }
                }
            }
            return dbparameter;
        }

        /// <summary>
        /// 构建 参数化 对象
        /// </summary>
        /// <param name="parameterName">参数化 变量名</param>
        /// <param name="value">参数化 值</param>
        /// <param name="type">数据类型</param>
        /// <param name="direction">参数化的方式</param>
        /// <returns></returns>
        public DbParameter CreateParameter(string parameterName, object value, DbType type, ParameterDirection direction = ParameterDirection.Input)
        {
            DbParameter dbparameter = DBFactory.CreateParameter();
            dbparameter.ParameterName = DBType.ParameterChar() + parameterName;
            dbparameter.Value = value;
            dbparameter.DbType = type;
            dbparameter.Direction = direction;
            return dbparameter;
        }


        public DbType GetDefDbType(string tableName,string columnName)
        {
            return DBType.GetDbType(Info[tableName, columnName]);
        }



        #region internal
        internal DbConnection CreateConn(string connectionString)
        {
            DbConnection _DBConn = DBFactory.CreateConnection();
            if (!string.IsNullOrWhiteSpace(connectionString))
            {
                _DBConn.ConnectionString = connectionString;
            }
            return _DBConn;
        }

        internal DbCommand CreateCmd()
        {
            DbCommand _DBCmd = DBFactory.CreateCommand();
            return _DBCmd;
        }

        internal DbCommand CreateCmd(string commandText = null, DbConnection DbConn = null)
        {
            DbCommand _DBCmd = DBFactory.CreateCommand();
            if (DbConn != null)
            {
                _DBCmd.Connection = DbConn;
            }
            if (!string.IsNullOrWhiteSpace(commandText))
            {
                _DBCmd.CommandText = commandText;
            }
            return _DBCmd;
        }
        
        internal DbDataAdapter CreateAdapter(DbCommand dbCmd = null)
        {
            DbDataAdapter dbadapter = DBFactory.CreateDataAdapter();
            if (dbCmd != null)
            {
                dbadapter.SelectCommand = dbCmd;
            }
            return dbadapter;
        }

        internal DbDataAdapter CreateAdapter(string commandText, DbConnection DbConn = null)
        {
            DbDataAdapter dbadapter = DBFactory.CreateDataAdapter();
            if (!string.IsNullOrWhiteSpace(commandText))
            {
                var cmd = CreateConn().CreateCommand();
                cmd.CommandText = commandText;
                dbadapter.SelectCommand = cmd;
            }
            return dbadapter;
        }

        internal DbParameter CreateParameter()
        {
            DbParameter dbparameter = DBFactory.CreateParameter();
            return dbparameter;
        }

        internal void CheckTabStuct(string tableName, params string[] columnNames)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                throw new ArgumentNullException("tableName", "不能为空！");
            }

            if (!Info.TableNames.Contains(tableName, StringComparer.OrdinalIgnoreCase))
            {
                throw new ArgumentException(string.Format("不存在该表！{0}", "[" + tableName + "]"), "tableName:" + tableName);
            }

            if (columnNames != null && columnNames.Length > 0)
            {
                List<string> lstAllColName = Info[tableName];

                foreach (string columnName in columnNames)
                {
                    if (!lstAllColName.Contains(columnName, StringComparer.OrdinalIgnoreCase))
                    {
                        throw new ArgumentException(string.Format("不存在该列！{0}", "[" + tableName + "." + columnName + "]"), "columnName:" + columnName, null);
                    }
                }
            }
        }

        #endregion


        public DB(DBType dbType, DbProviderFactory dbFactory, string connectionString)
        {
            this.DBType = dbType;
            this.DBFactory = dbFactory;
            this.ConnectionString = connectionString;

            this.ConnectionStringBuilder = DBFactory.CreateConnectionStringBuilder();
            this.ConnectionStringBuilder.ConnectionString = connectionString;

            this.ExcludeInsertColumns = new ExcludeColumnList(this);

            {//查询数据库表列信息前，先验证数据库能否可以连接成功！

                try
                {
                    using (DbConnection conn = CreateConn())
                    {
                        conn.Open();
                    }
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(string.Empty, null, ex);

                    throw ex;
                }
            }
        }

        internal void PrepareCommand<P>(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, P cmdParms, int times = 30, CommandType cmdType = CommandType.Text) where P:class
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (times > 0)//（以秒为单位）。默认为 30 秒
            {
                cmd.CommandTimeout = times;
            }
            else
            {
                cmd.CommandTimeout = 30;
            }

            if (cmdParms != null)
            {
                if (TypeInfo<P>.IsArrayParameter)
                {
                    Array arr = cmdParms as Array;
                    cmd.Parameters.AddRange(arr);
                }
                else if (TypeInfo<P>.IsListParameter)
                {
                    List<DbParameter> lstDp = cmdParms as List<DbParameter>;
                    cmd.Parameters.AddRange(lstDp.ToArray());
                }
                else if (TypeInfo<P>.IsAnonymousType)
                {
                    TypeInfo<P>.SetParam<P>(cmd, cmdParms, this.DBType);
                }
                else if (TypeInfo<P>.IsDict)
                {
                    TypeInfo<P>.SetParam(cmd, (IDictionary)cmdParms, this.DBType);
                }
                else if (TypeInfo<P>.IsNameValueColl)
                {
                    TypeInfo<P>.SetParam(cmd, cmdParms as NameValueCollection, this.DBType);
                }
            }

            if (OnExecuting != null)
            {
                OnExecuting.Invoke(cmd.CommandText, cmd.Parameters);
            }

            if (OnExecutingChange != null)
            {
                var paraKV = OnExecutingChange.Invoke(cmd.CommandText, cmd.Parameters);

                cmd.CommandText = paraKV.Key;
                cmd.Parameters.Clear();

                if (paraKV.Value != null)
                {
                    cmd.Parameters.AddRange(paraKV.Value);
                }
            }
        }

        /// <summary>
        /// 尝试 连接字符串 能否 连接成功
        /// </summary>
        /// <returns></returns>
        public virtual bool TryConnect()
        {
            try
            {
                using (DbConnection conn = CreateConn())
                {
                    conn.Open();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 验证 执行语句 是否 能执行通过
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="ex"></param>
        /// <returns></returns>
        public virtual bool ValidateSql(string strSql, out Exception ex)
        {
            bool bResult = false;
            ex = null;
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                conn.Open();
                try
                {
                    cmd.CommandText = "explain  " + strSql;
                    cmd.ExecuteNonQuery();
                    bResult = true;
                }
                catch (Exception e)
                {
                    ex = e;
                    bResult = false;
                }
                finally
                {
                    cmd?.Dispose();
                }
            }
            return bResult;           
        }

        #region 基础查询
        public virtual TRet Single<TRet>(string strSql, TRet defRet, int timeOut = 30)
        {
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                TRet result;
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                    object obj = cmd.ExecuteScalar();
                    result = obj.ChangeType<TRet>(default(TRet));
                }
                catch
                {
                    result = defRet;
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, result);
                }

                return result;
            }
        }

        public virtual TRet Single<TRet, P>(string strSql, TRet defRet, P param = null, int timeOut = 30) where P : class
        {
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                TRet result;
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                    object obj = cmd.ExecuteScalar();
                    result = obj.ChangeType<TRet>(default(TRet));
                }
                catch
                {
                    result = defRet;
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, result);
                }
                return result;
            }
        }

        public virtual DataRow FirstRow(string strSql, int timeOut = 30)
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    DataRow dr = null;
                    if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, dr);
                    }

                    return dr;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);
                    throw ex;
                }
            }
        }

        public virtual DataRow FirstRow<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);
                    DataRow dr = null;
                    if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, dr);
                    }

                    return dr;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
        }

        public virtual DataTable QueryTable(string strSql, int timeOut = 30)
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    DataTable dt = null;
                    if (ds.Tables.Count > 0)
                    {
                        dt = ds.Tables[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, dt);
                    }

                    return dt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);
                    throw ex;
                }
            }
        }

        public virtual DataTable QueryTable<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);
                    DataTable dt = null;

                    if (ds.Tables.Count > 0)
                    {
                        dt = ds.Tables[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, dt);
                    }
                    
                    return dt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
        }

        public virtual List<DataTable> QueryListTable(string strSql, int timeOut = 30)
        {
            List<DataTable> lstTabs = new List<DataTable>();
            DataSet ds = new DataSet("ds");
            DbCommand cmd = null;
            using (DbConnection conn = CreateConn())
            {
                cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);                    
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);
                    throw ex;
                }
            }
            if (ds.Tables.Count > 0)
            {
                foreach (DataTable tb in ds.Tables)
                {
                    lstTabs.Add(tb);
                }
            }

            if (OnExecuted != null)
            {
                OnExecuted.Invoke(cmd?.CommandText, null, lstTabs);
            }

            return lstTabs;
        }

        public virtual List<DataTable> QueryListTable<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            List<DataTable> lstTabs = new List<DataTable>();
            DataSet ds = new DataSet("ds");
            DbCommand cmd = null;
            using (DbConnection conn = CreateConn())
            {
                cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, ds);
                    }

                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
            if (ds.Tables.Count > 0)
            {
                foreach (DataTable tb in ds.Tables)
                {
                    lstTabs.Add(tb);
                }
            }

            if (OnExecuted != null)
            {
                OnExecuted.Invoke(cmd?.CommandText, null, lstTabs);
            }

            return lstTabs;
        }

        public virtual DataSet QueryDS(string strSql, int timeOut = 30)
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, ds);
                    }

                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);
                    throw ex;
                }
            }
            return ds;
        }

        public virtual DataSet QueryDS<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, ds);
                    }

                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
            return ds;
        }

        public virtual TRet ReadSingle<TRet>(string strSql,TRet defRet, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            TRet result;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                myReader = cmd.ExecuteReader(CommandBehavior.SingleResult);                
                if (myReader.HasRows)
                {
                    myReader.Read();
                    // ret = myReader.GetFieldValue<TRet>(0);
                    result = myReader.GetValue(0).ChangeType<TRet>(default(TRet));
                }
                else
                {
                    result = defRet;
                }
            }
            catch 
            {
                result = defRet;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }

            if (OnExecuted != null)
            {
                OnExecuted.Invoke(cmd.CommandText, null, result);
            }
            return result;
        }


        public virtual TRet ReadSingle<TRet, P>(string strSql, TRet defRet, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            TRet result;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                myReader = cmd.ExecuteReader(CommandBehavior.SingleResult);
                if (myReader.HasRows)
                {
                    myReader.Read();
                    // ret = myReader.GetFieldValue<TRet>(0);
                    result = myReader.GetValue(0).ChangeType<TRet>(default(TRet));
                }
                else
                {
                    result = defRet;
                }
            }
            catch
            {
                result = defRet;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }

            if (OnExecuted != null)
            {
                OnExecuted.Invoke(cmd.CommandText, null, result);
            }
            return result;
        }

        public virtual Dictionary<string, object> ReadFirstRow(string strSql, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                myReader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                Dictionary<string, object> dict = new Dictionary<string, object>();
                if (myReader.HasRows)
                {
                    myReader.Read();
                    for (int j = 0; j < myReader.FieldCount; j++)
                    {
                        dict.Add(myReader.GetName(j), myReader.GetValue(j));
                    }
                }
                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, dict);
                }
                return dict;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }


        public virtual Dictionary<string, object> ReadFirstRow<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                myReader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                Dictionary<string, object> dict = new Dictionary<string, object>();
                if (myReader.HasRows)
                {
                    myReader.Read();
                    for (int j = 0; j < myReader.FieldCount; j++)
                    {
                        dict.Add(myReader.GetName(j), myReader.GetValue(j));
                    }
                }
                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, dict);
                }
                return dict;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }


        public virtual DataTable ReadTable(string strSql, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            DataTable data = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                myReader = cmd.ExecuteReader();
                data = new DataTable("table");
                if (myReader.HasRows)
                {            
                    bool isAddCol = false;
                    while (myReader.Read())
                    {
                        DataRow dr = data.NewRow();
                        for (int j = 0; j < myReader.FieldCount; j++)
                        {
                            string columnName = myReader.GetName(j);
                            if (!isAddCol)
                            {
                                data.Columns.Add(columnName, myReader.GetFieldType(j));
                            }

                            dr[columnName] = myReader[columnName];
                        }
                        data.Rows.Add(dr);
                        isAddCol = true;
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, data);
                }
                return data;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        #region Entity List NameValueCollection Dictionary 

        public List<TEntity> ReadEntitys<TEntity>(string strSql, Func<DbDataReader, List<TEntity>> convertTo, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            List<TEntity> lstVal = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                myReader = cmd.ExecuteReader();
                lstVal = new List<TEntity>();
                if (myReader.HasRows)
                {
                    lstVal = convertTo(myReader);
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, lstVal);
                }
                return lstVal;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        public List<TEntity> ReadEntitys<TEntity, P>(string strSql, Func<DbDataReader, List<TEntity>> convertTo, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            List<TEntity> lstVal = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                myReader = cmd.ExecuteReader();
                lstVal = new List<TEntity>();
                if (myReader.HasRows)
                {
                    lstVal = convertTo(myReader);
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, lstVal);
                }
                return lstVal;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        public List<TRet> ReadList<TRet>(string strSql, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            List<TRet> lstVal = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                myReader = cmd.ExecuteReader();
                lstVal = new List<TRet>();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        lstVal.Add(myReader.GetFieldValue<TRet>(0));
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, lstVal);
                }
                return lstVal;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        public List<TRet> ReadList<TRet, P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            List<TRet> lstVal = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                myReader = cmd.ExecuteReader();
                lstVal = new List<TRet>();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        lstVal.Add(myReader.GetFieldValue<TRet>(0));
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, lstVal);
                }
                return lstVal;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        public NameValueCollection ReadNameValues(string strSql, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            NameValueCollection nvc = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                myReader = cmd.ExecuteReader();
                nvc = new NameValueCollection();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        nvc.Add(myReader.GetString(0), myReader.GetString(1));
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, nvc);
                }
                return nvc;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        public NameValueCollection ReadNameValues<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            NameValueCollection nvc = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                myReader = cmd.ExecuteReader();
                nvc = new NameValueCollection();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        nvc.Add(myReader.GetString(0), myReader.GetString(1));
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, nvc);
                }
                return nvc;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        public Dictionary<TKey, TValue> ReadDictionary<TKey, TValue>(string strSql, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            Dictionary<TKey, TValue> dict = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                myReader = cmd.ExecuteReader();
                dict = new Dictionary<TKey, TValue>();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        dict.Add(myReader.GetFieldValue<TKey>(0), myReader.GetFieldValue<TValue>(1));
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, dict);
                }
                return dict;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }

        public Dictionary<TKey, TValue> ReadDictionary<TKey, TValue, P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            Dictionary<TKey,TValue> dict = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                myReader = cmd.ExecuteReader();
                dict = new Dictionary<TKey, TValue>();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        dict.Add(myReader.GetFieldValue<TKey>(0), myReader.GetFieldValue<TValue>(1));
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, dict);
                }
                return dict;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }
        #endregion
        public virtual DataTable ReadTable<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbDataReader myReader = null;
            DataTable data = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                myReader = cmd.ExecuteReader();
                data = new DataTable("table");
                if (myReader.HasRows)
                {
                    bool isAddCol = false;
                    while (myReader.Read())
                    {
                        DataRow dr = data.NewRow();
                        for (int j = 0; j < myReader.FieldCount; j++)
                        {
                            string columnName = myReader.GetName(j);
                            if (!isAddCol)
                            {
                                data.Columns.Add(columnName, myReader.GetFieldType(j));
                            }

                            dr[columnName] = myReader[columnName];
                        }
                        data.Rows.Add(dr);
                        isAddCol = true;
                    }
                }

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, data);
                }
                return data;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
            finally
            {
                myReader?.Close();
                conn?.Close();
            }
        }


        public virtual DbDataReader Reader(string strSql, int timeOut = 30)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            try
            {
                conn = CreateConn();
                cmd = conn.CreateCommand();
                PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, null, myReader);
                }

                return myReader;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, null, ex);
                throw ex;
            }
        }

        public virtual DbDataReader Reader<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            try
            {
                conn = CreateConn();
                PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (OnExecuted != null)
                {
                    OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, myReader);
                }

                return myReader;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(strSql, cmd.Parameters, ex);
                throw ex;
            }
        }

        public virtual DataSet QueryDSByProc(string storedProcName, int timeOut = 30)
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, storedProcName, (object)null, timeOut, CommandType.StoredProcedure);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, ds);
                    }

                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);
                    throw ex;
                }
            }
            return ds;
        }

        public virtual DataSet QueryDSByProc<P>(string storedProcName, P param = null, int timeOut = 30) where P : class
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, storedProcName, param, timeOut, CommandType.StoredProcedure);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, ds);
                    }

                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
            return ds;
        }

        public virtual DataTable QueryTableByProc(string storedProcName, int timeOut = 30)
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, storedProcName, (object)null, timeOut, CommandType.StoredProcedure);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    DataTable dt = null;
                    if (ds.Tables.Count > 0)
                    {
                        dt = ds.Tables[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, dt);
                    }

                    return dt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);
                    throw ex;
                }
            }
        }

        public virtual DataTable QueryTableByProc<P>(string storedProcName, P param = null, int timeOut = 30) where P : class
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, storedProcName, param, timeOut, CommandType.StoredProcedure);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    DataTable dt = null;
                    if (ds.Tables.Count > 0)
                    {
                        dt = ds.Tables[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, dt);
                    }
                    return dt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
        }
        #endregion

        #region 执行
        public virtual int ExecSql(string strSql, int timeOut = 30)
        {
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, (object)null, timeOut);
                    int cnt = cmd.ExecuteNonQuery();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, cnt);
                    }

                    return cnt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(strSql, null, ex);
                    throw ex;
                }
            }
        }

        public virtual int ExecSql<P>(string strSql, P param = null, int timeOut = 30) where P : class
        {
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, param, timeOut);
                    int cnt = cmd.ExecuteNonQuery();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, cnt);
                    }

                    return cnt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(strSql, cmd.Parameters, ex);
                    throw ex;
                }
            }
        }

        public virtual int ExecSqlTran(string[] sqlCmds, int timeOut = 30)
        {
            if (sqlCmds == null || sqlCmds.Length == 0)
            {
                return -1;
            }
            using (DbConnection conn = CreateConn())
            {
                conn.Open();
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = timeOut;

                DbTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;

                try
                {
                    int cnt = 0;
                    for (int n = 0; n < sqlCmds.Length; n++)
                    {
                        string strsql = sqlCmds[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;

                            if (OnExecuting != null)
                            {
                                OnExecuting.Invoke(cmd.CommandText, null);
                            }

                            if (OnExecutingChange != null)
                            {
                                var paraKV = OnExecutingChange.Invoke(cmd.CommandText, null);

                                cmd.CommandText = paraKV.Key;
                                cmd.Parameters.Clear();

                                if (paraKV.Value != null)
                                {
                                    cmd.Parameters.AddRange(paraKV.Value);
                                }
                            }

                            int res = cmd.ExecuteNonQuery();
                            cnt += res;

                            if (OnExecuted != null)
                            {
                                OnExecuted.Invoke(cmd.CommandText, null, res);
                            }
                        }
                    }
                    tran.Commit();
                    return cnt;
                }
                catch (Exception ex)
                {
                    tran.Rollback();

                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);

                    return -1;
                }
            }
        }

        public virtual int ExecSqlTran(params string[] sqlCmds)
        {
            return ExecSqlTran(sqlCmds, 30);
        }

        public virtual int ExecSqlTran(List<KeyValuePair<string, List<DbParameter>>> strSqlList, int timeOut = 30)
        {
            using (DbConnection conn = CreateConn())
            {
                conn.Open();
                using (DbTransaction trans = conn.BeginTransaction())
                {
                    DbCommand cmd = conn.CreateCommand();
                    try
                    {
                        int count = 0;
                        foreach (var kv in strSqlList)
                        {
                            cmd.CommandText = kv.Key;
                            if (kv.Value != null && kv.Value.Count > 0)
                            {
                                cmd.Parameters.AddRange(kv.Value.ToArray());
                            }

                            if (OnExecuting != null)
                            {
                                OnExecuting.Invoke(cmd.CommandText, cmd.Parameters);
                            }

                            if (OnExecutingChange != null)
                            {
                                var paraKV = OnExecutingChange.Invoke(cmd.CommandText, cmd.Parameters);

                                cmd.CommandText = paraKV.Key;
                                cmd.Parameters.Clear();

                                if (paraKV.Value != null)
                                {
                                    cmd.Parameters.AddRange(paraKV.Value);
                                }
                            }

                            int res = cmd.ExecuteNonQuery();
                            count += res;
                            cmd.Parameters.Clear();

                            if (OnExecuted != null)
                            {
                                OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, res);
                            }
                        }
                        trans.Commit();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();

                        if (this.OnError != null)
                            this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);

                        return -1;
                    }
                }
            }
        }

        public virtual int ExecProc(string procName, int timeOut = 30)
        {
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, procName, (object)null, timeOut, CommandType.StoredProcedure);
                    int cnt = cmd.ExecuteNonQuery();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, null, cnt);
                    }

                    return cnt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, null, ex);
                    throw ex;
                }
            }
        }

        public virtual int ExecProc<P>(string procName, P param = null, int timeOut = 30) where P : class
        {
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, procName, param, timeOut, CommandType.StoredProcedure);
                    int cnt = cmd.ExecuteNonQuery();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, cnt);
                    }

                    return cnt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
        }
        #endregion

        #region BulkCopy
        public virtual bool BulkCopy(DataTable data, string tableName, Dictionary<string, string> columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            throw new NotImplementedException("该方法未支持！(" + this.DBType + ")");
        }

        public virtual bool BulkCopy<P>(DataTable data, string tableName, P columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60) where P : class
        {
            throw new NotImplementedException("该方法未支持！(" + this.DBType + ")");
        }

        public virtual bool BulkCopy(DbDataReader reader, string tableName, Dictionary<string, string> columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            throw new NotImplementedException("该方法未支持！(" + this.DBType + ")");
        }

        public virtual bool BulkCopy<P>(DbDataReader reader, string tableName, P columnMappings = null, int batchSize = 200000, int bulkCopyTimeout = 60) where P : class
        {
            throw new NotImplementedException("该方法未支持！(" + this.DBType + ")");
        }

        #endregion

        public virtual bool ExistByColVal<P>(string tableName, string columnName, object columnValue, params P[] excludeValues)
        {
            CheckTabStuct(tableName, columnName);
            string exist_sql = "select count(1) from " + tableName + " where " + columnName + "='" + columnValue + "' ";
            if (excludeValues != null && excludeValues.Length > 0)
            {
                string in_sql = SqlScript.SqlIn(columnName, excludeValues, true);
                exist_sql += in_sql;
            }
            return Single<int>(exist_sql,0) > 0;
        }

        public KeyValuePair<string,List<DbParameter>> InsertScript<DT>(DT data, string tableName, params string[] excludeColNames)
        {
            if (data == null)
                throw new ArgumentException("data", "不能为null!");
            CheckTabStuct(tableName);

            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("insert into " + tableName + " (");
            StringBuilder sb_afterSQl = new StringBuilder();
            sb_afterSQl.Append(") values (");

            string insert_sql = string.Empty;

            List<DbParameter> lstPara = new List<DbParameter>();
            var lstColName = Info[tableName];

            IEnumerable<string> enumNames = new List<string>();
            if (TypeInfo<DT>.IsAnonymousType)
            {
                var curNames = TypeInfo<DT>.PropNames;
                enumNames = curNames.Intersect(lstColName, StringComparer.OrdinalIgnoreCase);

                if (excludeColNames != null && excludeColNames.Length > 0)
                {
                    enumNames = enumNames.Except(excludeColNames, StringComparer.OrdinalIgnoreCase);
                }

                string[] columnNames = ExcludeInsertColumns.Coll.GetValues(tableName);

                if (columnNames != null && columnNames.Length > 0)
                {
                    enumNames = enumNames.Except(columnNames, StringComparer.OrdinalIgnoreCase);
                }

                foreach (var colName in enumNames)
                {
                    ColumnInfo colInfo = Info[tableName, colName];
                    if (colInfo.IsIdentity)
                    {
                        continue;
                    }
                    object obj = TypeInfo<DT>.PropMapping[colName].GetValue(data, null);
                    DbParameter dp = CreateParameter(colName, obj, colInfo);
                    lstPara.Add(dp);

                    sb_beforeSQL.Append(colName + ",");
                    if (DBType != DBType.OracleDDTek)
                    {
                        sb_afterSQl.Append(dp.ParameterName + ",");
                    }
                    else
                    {
                        sb_afterSQl.Append("?" + ",");
                    }
                }

            }
            else if (TypeInfo<DT>.IsNameValueColl)
            {
                var curNames = (data as NameValueCollection).AllKeys;
                enumNames = curNames.Intersect(lstColName, StringComparer.OrdinalIgnoreCase);

                if (excludeColNames != null && excludeColNames.Length > 0)
                {
                    enumNames = enumNames.Except(excludeColNames, StringComparer.OrdinalIgnoreCase);
                }

                foreach (var colName in enumNames)
                {
                    ColumnInfo colInfo = Info[tableName, colName];
                    if (colInfo.IsIdentity)
                    {
                        continue;
                    }
                    object obj = (data as NameValueCollection)[colName];
                    DbParameter dp = CreateParameter(colName, obj, colInfo);
                    lstPara.Add(dp);

                    sb_beforeSQL.Append(colName + ",");
                    if (DBType != DBType.OracleDDTek)
                    {
                        sb_afterSQl.Append(dp.ParameterName + ",");
                    }
                    else
                    {
                        sb_afterSQl.Append("?" + ",");
                    }
                }
            }
            else if (TypeInfo<DT>.IsDict)
            {
                var curNames = (data as IDictionary<string, object>).Keys;

                enumNames = curNames.Intersect(lstColName, StringComparer.OrdinalIgnoreCase);

                if (excludeColNames != null && excludeColNames.Length > 0)
                {
                    enumNames = enumNames.Except(excludeColNames, StringComparer.OrdinalIgnoreCase);
                }

                foreach (var colName in enumNames)
                {
                    ColumnInfo colInfo = Info[tableName, colName];
                    if (colInfo.IsIdentity)
                    {
                        continue;
                    }
                    object obj = (data as IDictionary<string, object>)[colName];
                    DbParameter dp = CreateParameter(colName, obj, colInfo);
                    lstPara.Add(dp);

                    sb_beforeSQL.Append(colName + ",");
                    if (DBType != DBType.OracleDDTek)
                    {
                        sb_afterSQl.Append(dp.ParameterName + ",");
                    }
                    else
                    {
                        sb_afterSQl.Append("?" + ",");
                    }
                }
            }
            else
            {
                throw new ArgumentException("未知数据类型插入！", "data");
            }

            if (!enumNames.Any())
            {
                throw new ArgumentException("至少有1列的值才能够插入！", "data");
            }

            insert_sql = sb_beforeSQL.ToString().TrimEnd(',') + sb_afterSQl.ToString().TrimEnd(',') + ")";

            return new KeyValuePair<string, List<DbParameter>>(insert_sql, lstPara);
        }

        public virtual bool Insert<DT>(DT data, string tableName, params string[] excludeColNames)
        {
            var kv = InsertScript(data, tableName, excludeColNames);
            return ExecSql(kv.Key, kv.Value) > 0;
        }

        internal virtual Ret InsertGet<DT,Ret>(DT data, string tableName, params string[] excludeColNames)
        {
            var kv = InsertScript(data, tableName, excludeColNames);
            string insert_Sql = kv.Key + ";" + SqlScript.IdentitySql(DBType, tableName);
            return Single<Ret, List<DbParameter>>(insert_Sql, default(Ret), kv.Value);
        }

        public virtual int InsertGetInt<DT>(DT data, string tableName, params string[] excludeColNames)
        {
            return InsertGet<DT, int>(data, tableName, excludeColNames);
        }

        public virtual long InsertGetLong<DT>(DT data, string tableName, params string[] excludeColNames)
        {
            return InsertGet<DT, long>(data, tableName, excludeColNames);
        }

        public KeyValuePair<string, List<DbParameter>> UpdateScript<DT>(DT data, string tableName, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            if (data == null)
                throw new ArgumentException("data", "不能为null!");
            CheckTabStuct(tableName, pkOrUniqueColName);

            string parameterChar = DBType.ParameterChar();

            StringBuilder sb_beforeSQL = new StringBuilder();
            sb_beforeSQL.Append("update " + tableName + " set ");
                       

            string update_sql = string.Empty;
            DbParameter paraPKOrUnique = null;
            string paraPKOrUniqueName = string.Empty;

            List<DbParameter> lstPara = new List<DbParameter>();
            var lstColName = Info[tableName];

            IEnumerable<string> enumNames = new List<string>();
            if (TypeInfo<DT>.IsAnonymousType)
            {
                var curNames = TypeInfo<DT>.PropNames;
                enumNames = curNames.Intersect(lstColName, StringComparer.OrdinalIgnoreCase);

                if (excludeColNames != null && excludeColNames.Length > 0)
                {
                    enumNames = enumNames.Except(excludeColNames, StringComparer.OrdinalIgnoreCase);
                }

                foreach (var colName in enumNames)
                {
                    ColumnInfo colInfo = Info[tableName, colName];
                    object obj = TypeInfo<DT>.PropMapping[colName].GetValue(data, null);
                    if (!colName.Equals(pkOrUniqueColName, StringComparison.OrdinalIgnoreCase))
                    {
                        sb_beforeSQL.Append(colName + "=" + (DBType == DBType.OracleDDTek ? "?" : parameterChar + colName) + ",");
                        DbParameter dp = CreateParameter(colName, obj, colInfo);
                        lstPara.Add(dp);
                    }
                    else
                    {
                        paraPKOrUnique = CreateParameter(colName, obj, colInfo);
                        paraPKOrUniqueName = colName;
                    }
                }

            }
            else if (TypeInfo<DT>.IsNameValueColl)
            {
                var curNames = (data as NameValueCollection).AllKeys;
                enumNames = curNames.Intersect(lstColName, StringComparer.OrdinalIgnoreCase);

                if (excludeColNames != null && excludeColNames.Length > 0)
                {
                    enumNames = enumNames.Except(excludeColNames, StringComparer.OrdinalIgnoreCase);
                }

                foreach (var colName in enumNames)
                {
                    ColumnInfo colInfo = Info[tableName, colName];                   
                    object obj = (data as NameValueCollection)[colName];
                    if (!colName.Equals(pkOrUniqueColName, StringComparison.OrdinalIgnoreCase))
                    {
                        sb_beforeSQL.Append(colName + "=" + (DBType == DBType.OracleDDTek ? "?" : parameterChar + colName) + ",");
                        DbParameter dp = CreateParameter(colName, obj, colInfo);
                        lstPara.Add(dp);
                    }
                    else
                    {
                        paraPKOrUnique = CreateParameter(colName, obj, colInfo);
                        paraPKOrUniqueName = colName;
                    }
                }
            }
            else if (TypeInfo<DT>.IsDict)
            {
                var curNames = (data as IDictionary<string, object>).Keys;

                enumNames = curNames.Intersect(lstColName, StringComparer.OrdinalIgnoreCase);

                if (excludeColNames != null && excludeColNames.Length > 0)
                {
                    enumNames = enumNames.Except(excludeColNames, StringComparer.OrdinalIgnoreCase);
                }

                foreach (var colName in enumNames)
                {
                    ColumnInfo colInfo = Info[tableName, colName];
                    object obj = (data as IDictionary<string, object>)[colName];
                    if (!colName.Equals(pkOrUniqueColName, StringComparison.OrdinalIgnoreCase))
                    {
                        sb_beforeSQL.Append(colName + "=" + (DBType == DBType.OracleDDTek ? "?" : parameterChar + colName) + ",");
                        DbParameter dp = CreateParameter(colName, obj, colInfo);
                        lstPara.Add(dp);
                    }
                    else
                    {
                        paraPKOrUnique = CreateParameter(colName, obj, colInfo);
                        paraPKOrUniqueName = colName;
                    }
                }
            }
            else
            {
                throw new ArgumentException("未知数据类型插入！", "data");
            }

            if (!enumNames.Any())
            {
                throw new ArgumentException("至少有1列的值才能够更新！", "data");
            }

            update_sql = sb_beforeSQL.ToString().TrimEnd(',') + (" where " + paraPKOrUniqueName + "=" + (DBType == DBType.OracleDDTek ? "?" : parameterChar + paraPKOrUniqueName));
            lstPara.Add(paraPKOrUnique);
            return new KeyValuePair<string, List<DbParameter>>(update_sql, lstPara);
        }

        public virtual bool Update<DT>(DT data, string tableName, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            var kv = UpdateScript(data, tableName, pkOrUniqueColName, excludeColNames);
            return ExecSql(kv.Key, kv.Value) > 0;
        }


        public KeyValuePair<string, List<DbParameter>> UpsertScript<DT>(DT data, string tableName, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            if (data == null)
                throw new ArgumentException("data", "不能为null!");
            CheckTabStuct(tableName, pkOrUniqueColName);

            var tableInfo = Info.TableInfoDict[tableName];

            if (tableInfo.PriKeyType == PrimaryKeyType.UNKNOWN)
            {
                return InsertScript<DT>(data, tableName, pkOrUniqueColName);
            }
            else
            {
                object pkOrUniqueColValue = null;

                if (TypeInfo<DT>.IsAnonymousType)
                {
                    PropertyInfo pkOrUniqueColPy;
                    if (TypeInfo<DT>.PropMapping.TryGetValue(pkOrUniqueColName, out pkOrUniqueColPy))
                    {
                        pkOrUniqueColValue = pkOrUniqueColPy.GetValue(data, null);
                    }
                }
                else if (TypeInfo<DT>.IsNameValueColl)
                {
                    var nvc = (data as NameValueCollection);
                    pkOrUniqueColValue = nvc[pkOrUniqueColName];
                }
                else if (TypeInfo<DT>.IsDict)
                {
                    var dict = (data as IDictionary<string, object>);
                    dict.TryGetValue(pkOrUniqueColName, out pkOrUniqueColValue);
                }
                else
                {
                    throw new ArgumentException("未知数据类型插入！", "data");
                }

                if (tableInfo.PriKeyType== PrimaryKeyType.AUTO)
                {
                    if (pkOrUniqueColValue == null)
                    {
                        return InsertScript<DT>(data, tableName, pkOrUniqueColName);
                    }
                    else
                    {
                        return UpdateScript<DT>(data, tableName, pkOrUniqueColName, excludeColNames);
                    }
                }
                else//PrimaryKeyType.SET
                {
                    if (ExistByColVal<object>(tableName, pkOrUniqueColName, pkOrUniqueColValue, null))
                    {
                        return UpdateScript<DT>(data, tableName, pkOrUniqueColName, excludeColNames);
                    }
                    else
                    {
                        return InsertScript<DT>(data, tableName, pkOrUniqueColName);
                    }
                }
            }
        }
        public virtual bool Upsert<DT>(DT data, string tableName, string pkOrUniqueColName = "Id", params string[] excludeColNames)
        {
            var kv = UpsertScript(data, tableName, pkOrUniqueColName, excludeColNames);
            return ExecSql(kv.Key, kv.Value) > 0;
        }

        public virtual bool UpSingle(string tableName, string columnName, object columnValue, object pkOrUniqueValue, string pkOrUniqueColName = "Id")
        {
            CheckTabStuct(tableName, columnName);
            string upSql = string.Empty;
            if (DBType == DBType.OracleDDTek)
            {
                upSql = "update " + tableName + " set " + columnName + "=? where " + pkOrUniqueColName + "=?";
            }
            else
            {
                upSql = "update " + tableName + " set " + columnName + "=" + DBType.ParameterChar() + columnName + " where " + pkOrUniqueColName + "=" + DBType.ParameterChar() + pkOrUniqueColName;
            }

            var p1 = CreateParameter(columnName, columnValue, Info[tableName, columnName]);
            var p2 = CreateParameter(pkOrUniqueColName, pkOrUniqueValue, Info[tableName, pkOrUniqueColName]);

            return ExecSql(upSql, new DbParameter[] { p1, p2 }) > 0;
        }

        public virtual int Delete<P>(string tableName, string columnName, params P[] columnValues)
        {
            if (columnValues == null || columnValues.Length <= 0)
            {
                return 0;
            }
            string delSql = "delete from " + tableName + " where 1=1 " + SqlScript.SqlIn<P>(columnName, columnValues);
            return ExecSql(delSql);
        }
        
        public KeyValuePair<DataTable, long> GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string whereStr, string orderbyStr)
        {
            long totalCount;
            DataTable data = GetDataTableByPager(currentPage, pageSize, selColumns, joinTableName, whereStr, orderbyStr, out totalCount);
            return new KeyValuePair<DataTable, long>(data, totalCount);
        }

        public virtual DataTable GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string whereStr, string orderbyStr, out long totalCount)
        {
            throw new NotImplementedException(DBType + "暂未支持");
        }

        
    }
}
